{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PB Python Excel Filter and Edit Example "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Full article is posted here - http://pbpython.com/excel-filter-edit.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>GP-14407</td>\n",
       "      <td>Belt</td>\n",
       "      <td>19</td>\n",
       "      <td>88.49</td>\n",
       "      <td>1681.31</td>\n",
       "      <td>2015-11-17 05:58:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>FI-01804</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>78.07</td>\n",
       "      <td>234.21</td>\n",
       "      <td>2016-02-13 04:04:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>530925</td>\n",
       "      <td>Purdy and Sons</td>\n",
       "      <td>Teagan O'Keefe</td>\n",
       "      <td>EO-54210</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>19</td>\n",
       "      <td>30.21</td>\n",
       "      <td>573.99</td>\n",
       "      <td>2015-08-11 12:44:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "0          680916            Mueller and Sons    Loring Predovic  GP-14407   \n",
       "1          680916            Mueller and Sons    Loring Predovic  FI-01804   \n",
       "2          530925              Purdy and Sons     Teagan O'Keefe  EO-54210   \n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  \n",
       "0     Belt        19       88.49    1681.31  2015-11-17 05:58:34  \n",
       "1    Shirt         3       78.07     234.21  2016-02-13 04:04:11  \n",
       "2    Shirt        19       30.21     573.99  2015-08-11 12:44:38  \n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50  \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"https://github.com/chris1610/pbpython/blob/master/data/sample-sales-reps.xlsx?raw=true\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a default commision rate of 2% for all sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "      <th>commission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>GP-14407</td>\n",
       "      <td>Belt</td>\n",
       "      <td>19</td>\n",
       "      <td>88.49</td>\n",
       "      <td>1681.31</td>\n",
       "      <td>2015-11-17 05:58:34</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>FI-01804</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>78.07</td>\n",
       "      <td>234.21</td>\n",
       "      <td>2016-02-13 04:04:11</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>530925</td>\n",
       "      <td>Purdy and Sons</td>\n",
       "      <td>Teagan O'Keefe</td>\n",
       "      <td>EO-54210</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>19</td>\n",
       "      <td>30.21</td>\n",
       "      <td>573.99</td>\n",
       "      <td>2015-08-11 12:44:38</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "0          680916            Mueller and Sons    Loring Predovic  GP-14407   \n",
       "1          680916            Mueller and Sons    Loring Predovic  FI-01804   \n",
       "2          530925              Purdy and Sons     Teagan O'Keefe  EO-54210   \n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  commission  \n",
       "0     Belt        19       88.49    1681.31  2015-11-17 05:58:34        0.02  \n",
       "1    Shirt         3       78.07     234.21  2016-02-13 04:04:11        0.02  \n",
       "2    Shirt        19       30.21     573.99  2015-08-11 12:44:38        0.02  \n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50        0.02  \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03        0.02  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"commission\"] = .02\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since shirts are high margin, adjust all products in the shirt categort with a commission rate of 2.5%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "      <th>commission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>GP-14407</td>\n",
       "      <td>Belt</td>\n",
       "      <td>19</td>\n",
       "      <td>88.49</td>\n",
       "      <td>1681.31</td>\n",
       "      <td>2015-11-17 05:58:34</td>\n",
       "      <td>0.020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>FI-01804</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>78.07</td>\n",
       "      <td>234.21</td>\n",
       "      <td>2016-02-13 04:04:11</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>530925</td>\n",
       "      <td>Purdy and Sons</td>\n",
       "      <td>Teagan O'Keefe</td>\n",
       "      <td>EO-54210</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>19</td>\n",
       "      <td>30.21</td>\n",
       "      <td>573.99</td>\n",
       "      <td>2015-08-11 12:44:38</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "0          680916            Mueller and Sons    Loring Predovic  GP-14407   \n",
       "1          680916            Mueller and Sons    Loring Predovic  FI-01804   \n",
       "2          530925              Purdy and Sons     Teagan O'Keefe  EO-54210   \n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  commission  \n",
       "0     Belt        19       88.49    1681.31  2015-11-17 05:58:34       0.020  \n",
       "1    Shirt         3       78.07     234.21  2016-02-13 04:04:11       0.025  \n",
       "2    Shirt        19       30.21     573.99  2015-08-11 12:44:38       0.025  \n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50       0.025  \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03       0.025  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df[\"category\"] == \"Shirt\", [\"commission\"]] = .025\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since there is a special program for selling 10 or more belts in a transaction, you get 4% commission!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "      <th>commission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>GP-14407</td>\n",
       "      <td>Belt</td>\n",
       "      <td>19</td>\n",
       "      <td>88.49</td>\n",
       "      <td>1681.31</td>\n",
       "      <td>2015-11-17 05:58:34</td>\n",
       "      <td>0.040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>FI-01804</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>78.07</td>\n",
       "      <td>234.21</td>\n",
       "      <td>2016-02-13 04:04:11</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>530925</td>\n",
       "      <td>Purdy and Sons</td>\n",
       "      <td>Teagan O'Keefe</td>\n",
       "      <td>EO-54210</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>19</td>\n",
       "      <td>30.21</td>\n",
       "      <td>573.99</td>\n",
       "      <td>2015-08-11 12:44:38</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "0          680916            Mueller and Sons    Loring Predovic  GP-14407   \n",
       "1          680916            Mueller and Sons    Loring Predovic  FI-01804   \n",
       "2          530925              Purdy and Sons     Teagan O'Keefe  EO-54210   \n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  commission  \n",
       "0     Belt        19       88.49    1681.31  2015-11-17 05:58:34       0.040  \n",
       "1    Shirt         3       78.07     234.21  2016-02-13 04:04:11       0.025  \n",
       "2    Shirt        19       30.21     573.99  2015-08-11 12:44:38       0.025  \n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50       0.025  \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03       0.025  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(df[\"category\"] == \"Belt\") & (df[\"quantity\"] >= 10), [\"commission\"]] = .04\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, some transactions can get a bonus and a commission increase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[\"bonus\"] = 0\n",
    "df.loc[(df[\"category\"] == \"Shoes\") & (df[\"ext price\"] >= 1000 ), [\"bonus\", \"commission\"]] = 250, 0.045"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "      <th>commission</th>\n",
       "      <th>bonus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>282122</td>\n",
       "      <td>Connelly, Abshire and Von</td>\n",
       "      <td>Beth Skiles</td>\n",
       "      <td>GJ-90272</td>\n",
       "      <td>Shoes</td>\n",
       "      <td>20</td>\n",
       "      <td>96.62</td>\n",
       "      <td>1932.40</td>\n",
       "      <td>2016-03-17 10:19:05</td>\n",
       "      <td>0.045</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>DU-87462</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>10</td>\n",
       "      <td>67.64</td>\n",
       "      <td>676.40</td>\n",
       "      <td>2015-11-25 22:05:36</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>218667</td>\n",
       "      <td>Jaskolski-O'Hara</td>\n",
       "      <td>Trish Deckow</td>\n",
       "      <td>DU-87462</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>11</td>\n",
       "      <td>91.86</td>\n",
       "      <td>1010.46</td>\n",
       "      <td>2016-04-24 15:05:58</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "5          282122   Connelly, Abshire and Von        Beth Skiles  GJ-90272   \n",
       "6          398620                  Brekke Ltd  Esequiel Schinner  DU-87462   \n",
       "7          218667            Jaskolski-O'Hara       Trish Deckow  DU-87462   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  commission  \\\n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50       0.025   \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03       0.025   \n",
       "5    Shoes        20       96.62    1932.40  2016-03-17 10:19:05       0.045   \n",
       "6    Shirt        10       67.64     676.40  2015-11-25 22:05:36       0.025   \n",
       "7    Shirt        11       91.86    1010.46  2016-04-24 15:05:58       0.025   \n",
       "\n",
       "   bonus  \n",
       "3      0  \n",
       "4      0  \n",
       "5    250  \n",
       "6      0  \n",
       "7      0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ix[3:7]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the compensation at the line item leve"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>customer name</th>\n",
       "      <th>sales rep</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "      <th>commission</th>\n",
       "      <th>bonus</th>\n",
       "      <th>comp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>GP-14407</td>\n",
       "      <td>Belt</td>\n",
       "      <td>19</td>\n",
       "      <td>88.49</td>\n",
       "      <td>1681.31</td>\n",
       "      <td>2015-11-17 05:58:34</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0</td>\n",
       "      <td>67.25240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>680916</td>\n",
       "      <td>Mueller and Sons</td>\n",
       "      <td>Loring Predovic</td>\n",
       "      <td>FI-01804</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>78.07</td>\n",
       "      <td>234.21</td>\n",
       "      <td>2016-02-13 04:04:11</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "      <td>5.85525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>530925</td>\n",
       "      <td>Purdy and Sons</td>\n",
       "      <td>Teagan O'Keefe</td>\n",
       "      <td>EO-54210</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>19</td>\n",
       "      <td>30.21</td>\n",
       "      <td>573.99</td>\n",
       "      <td>2015-08-11 12:44:38</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "      <td>14.34975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14406</td>\n",
       "      <td>Harber, Lubowitz and Fahey</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>12</td>\n",
       "      <td>90.29</td>\n",
       "      <td>1083.48</td>\n",
       "      <td>2016-01-23 02:15:50</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "      <td>27.08700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>398620</td>\n",
       "      <td>Brekke Ltd</td>\n",
       "      <td>Esequiel Schinner</td>\n",
       "      <td>NZ-99565</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>5</td>\n",
       "      <td>72.64</td>\n",
       "      <td>363.20</td>\n",
       "      <td>2015-08-10 07:16:03</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0</td>\n",
       "      <td>9.08000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number               customer name          sales rep       sku  \\\n",
       "0          680916            Mueller and Sons    Loring Predovic  GP-14407   \n",
       "1          680916            Mueller and Sons    Loring Predovic  FI-01804   \n",
       "2          530925              Purdy and Sons     Teagan O'Keefe  EO-54210   \n",
       "3           14406  Harber, Lubowitz and Fahey  Esequiel Schinner  NZ-99565   \n",
       "4          398620                  Brekke Ltd  Esequiel Schinner  NZ-99565   \n",
       "\n",
       "  category  quantity  unit price  ext price                 date  commission  \\\n",
       "0     Belt        19       88.49    1681.31  2015-11-17 05:58:34       0.040   \n",
       "1    Shirt         3       78.07     234.21  2016-02-13 04:04:11       0.025   \n",
       "2    Shirt        19       30.21     573.99  2015-08-11 12:44:38       0.025   \n",
       "3    Shirt        12       90.29    1083.48  2016-01-23 02:15:50       0.025   \n",
       "4    Shirt         5       72.64     363.20  2015-08-10 07:16:03       0.025   \n",
       "\n",
       "   bonus      comp  \n",
       "0      0  67.25240  \n",
       "1      0   5.85525  \n",
       "2      0  14.34975  \n",
       "3      0  27.08700  \n",
       "4      0   9.08000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"comp\"] = df[\"commission\"] * df[\"ext price\"] + df[\"bonus\"]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the commissions by sales rep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sales rep\n",
       "Ansley Cummings       2169.76\n",
       "Beth Skiles           3028.60\n",
       "Esequiel Schinner    10451.21\n",
       "Loring Predovic      10108.60\n",
       "Shannen Hudson        5275.66\n",
       "Teagan O'Keefe        7989.52\n",
       "Trish Deckow          5807.74\n",
       "Name: comp, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"sales rep\"])[\"comp\"].sum().round(2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
